Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.IO
Imports System.Text
Imports System.Data
Friend NotInheritable Class MSSQLConstruirSP
    Private Sub New()

    End Sub


    Friend Shared Sub CreateDeleteStoredProcedure(ByVal struct As DataTable, ByVal claves As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        If claves.PrimaryKey.Length > 0 Then
            'Create the stored procedure name
            Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
            Dim procedureName As String = tablename & "_BorrarxId"
            Dim fileName As String

            ' Determine the file name to be used
            If createMultipleFiles Then
                fileName = path & procedureName & ".sql"
            Else
                fileName = path & tablename & "_StoredProcedures.sql"
            End If

            Using writer As StreamWriter = New StreamWriter(fileName, False)
                'Create the seperator
                If createMultipleFiles = False Then
                    writer.WriteLine()
                    writer.WriteLine("/******************************************************************************")
                    writer.WriteLine("******************************************************************************/")
                End If

                'Create the drop statment
                writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & procedureName & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
                writer.WriteLine(Constants.vbTab & "drop procedure [dbo].[" & procedureName & "]")
                writer.WriteLine("GO")
                writer.WriteLine()

                'Create the SQL for the stored procedure
                writer.WriteLine("CREATE PROCEDURE [dbo].[" & procedureName & "] (")

                'Create the parameter list
                Dim i As Integer
                i = 0
                For Each column As DataColumn In claves.PrimaryKey
                    'Ignore any identity columns

                    Dim row As DataRow = column_metadata(struct, column.ColumnName.ToString)


                    'Append the column name as a parameter of the insert statement
                    If i > 0 Then
                        writer.WriteLine(Constants.vbTab + CreateParameterString(row, False) + ",")
                    Else
                        writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))


                    End If
                    i += 1
                Next column
                writer.WriteLine(")")

                writer.WriteLine()
                writer.WriteLine("AS")
                writer.WriteLine()
                writer.WriteLine("SET NOCOUNT ON")
                writer.WriteLine()
                writer.WriteLine("DELETE FROM")
                writer.WriteLine(Constants.vbTab & "[" & tablename & "]")
                writer.WriteLine("WHERE")

                'Create the where clause
                i = 0
                For Each column As DataColumn In claves.PrimaryKey


                    If i > 0 Then
                        writer.WriteLine(Constants.vbTab & "AND [" & column.ColumnName & "] = @" & column.ColumnName)
                    Else
                        writer.WriteLine(Constants.vbTab & "[" & column.ColumnName & "] = @" & column.ColumnName)
                    End If
                    i += 1
                Next column

                writer.WriteLine("GO")

                ' Create the grant statement, if a user was specified
                'If grantLoginName.Length > 0 Then
                '    writer.WriteLine()
                '    writer.WriteLine("GRANT EXECUTE ON [dbo].[" & procedureName & "] TO [" & grantLoginName & "]")
                '    writer.WriteLine("GO")
                'End If
            End Using
        End If
    End Sub
    Friend Shared Sub CreateInsertStoredProcedure(ByVal struct As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        ' Create the stored procedure name
        Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
        Dim procedureName As String = tablename + "_Agregar"
        Dim fileName As String

        ' Determine the file name to be used
        If createMultipleFiles Then
            fileName = path & procedureName + ".sql"
        Else
            fileName = path & tablename & "_StoredProcedures.sql"

        End If
        Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
            ' Create the seperator
            If (createMultipleFiles = False) Then
                writer.WriteLine()
                writer.WriteLine("/******************************************************************************")
                writer.WriteLine("******************************************************************************/")
            End If

            ' Create the drop statment
            writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
            writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
            writer.WriteLine("GO")
            writer.WriteLine()

            '              ' Create the SQL for the stored procedure
            writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "] (")

            ' Create the parameter list
            Dim i As Integer = 0
            For Each row As DataRow In struct.Rows


                If i = struct.Rows.Count - 1 Then
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))
                Else
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False) & ",")
                End If
                i += 1
            Next row
            writer.WriteLine(")")

            writer.WriteLine()
            writer.WriteLine("AS")
            writer.WriteLine()
            writer.WriteLine("SET NOCOUNT ON")
            writer.WriteLine()
            'Initialize all RowGuidCol columns
            For Each row As DataRow In struct.Rows
                If CType(row.Item("IS_ROWGUIDCOL"), Boolean) Then
                    writer.WriteLine("SET @" + row.Item("Column_Name").ToString + " = NEWID()")
                    writer.WriteLine()
                End If
            Next row

            writer.WriteLine("INSERT INTO [" + tablename + "] (")

            'Create the parameter list
            i = 0
            For Each row As DataRow In struct.Rows
                'Ignore any identity columns
                If CType(row.Item("IS_IDENTITY"), Boolean) = False Then
                    'Append the column name as a parameter of the insert statement
                    If i < struct.Rows.Count - 1 Then
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "],")
                    Else
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "]")

                    End If
                End If
                i += 1
            Next row

            writer.WriteLine(") VALUES (")

            'Create the values list
            i = 0
            For Each row As DataRow In struct.Rows
                'Ignore any identity columns
                If CType(row.Item("IS_IDENTITY"), Boolean) = False Then
                    'Append the column name as a parameter of the insert statement
                    If i < struct.Rows.Count - 1 Then
                        writer.WriteLine("@" + row.Item("Column_Name").ToString + ",")
                    Else
                        writer.WriteLine("@" + row.Item("Column_Name").ToString)

                    End If
                End If
                i += 1
            Next row


            writer.WriteLine(")")

            '	 Should we include a line for returning the identity?
            For Each row As DataRow In struct.Rows
                'Is the current column an identity column?
                If CType(row.Item("IS_IDENTITY"), Boolean) Then
                    writer.WriteLine()
                    writer.WriteLine("SELECT SCOPE_IDENTITY()")
                Else
                    If CType(row.Item("IS_ROWGUIDCOL"), Boolean) Then
                        writer.WriteLine()
                        writer.WriteLine("SELECT @" + row.Item("Column_Name").ToString)
                    End If
                End If
            Next row
            writer.WriteLine("GO")

            '		// Create the grant statement, if a user was specified
            '		if (grantLoginName.Length > 0) {
            '			writer.WriteLine();
            '			writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
            '			writer.WriteLine("GO");
            '		}
            '	}
            '}
        End Using

    End Sub

    Friend Shared Sub CreateUpdateStoredProcedure(ByVal struct As DataTable, ByVal claves As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        ' Create the stored procedure name
        Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
        Dim procedureName As String = tablename + "_Modificar"
        Dim fileName As String

        ' Determine the file name to be used
        If createMultipleFiles Then
            fileName = path & procedureName + ".sql"
        Else
            fileName = path & tablename & "_StoredProcedures.sql"

        End If
        Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
            ' Create the seperator
            If (createMultipleFiles = False) Then
                writer.WriteLine()
                writer.WriteLine("/******************************************************************************")
                writer.WriteLine("******************************************************************************/")
            End If

            ' Create the drop statment
            writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
            writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
            writer.WriteLine("GO")
            writer.WriteLine()

            '              ' Create the SQL for the stored procedure
            writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "] (")

            ' Create the parameter list
            Dim i As Integer = 0
            For Each row As DataRow In struct.Rows


                If i = struct.Rows.Count - 1 Then
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))
                Else
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False) & ",")
                End If
                i += 1
            Next row
            writer.WriteLine(")")

            writer.WriteLine()
            writer.WriteLine("AS")
            writer.WriteLine()
            writer.WriteLine("SET NOCOUNT ON")
            writer.WriteLine()
            writer.WriteLine("UPDATE")
            writer.WriteLine("[" + tablename + "]")
            writer.WriteLine("SET")
            i = 0
            For Each row As DataRow In struct.Rows
                'Ignore any identity columns


                If es_clave(claves, row.Item("Column_Name").ToString) = False Then
                    'Append the column name as a parameter of the insert statement
                    If i < struct.Rows.Count - 1 Then
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "] = @" + row.Item("Column_Name").ToString + ",")
                    Else
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "] = @" + row.Item("Column_Name").ToString)

                    End If
                End If
                i += 1
            Next row
            writer.WriteLine("WHERE")
            i = 0
            For Each column As DataColumn In claves.PrimaryKey
                'Ignore any identity columns

                'Append the column name as a parameter of the insert statement
                If i > 0 Then
                    writer.WriteLine(" AND [" + column.ColumnName.ToString + "] = @" + column.ColumnName.ToString + ",")
                Else
                    writer.WriteLine("[" + column.ColumnName.ToString + "] = @" + column.ColumnName.ToString)


                End If
                i += 1
            Next column
            writer.WriteLine()
            writer.WriteLine("GO")
        End Using
    End Sub
    Friend Shared Sub CreateSelectStoredProcedure(ByVal struct As DataTable, ByVal claves As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        ' Create the stored procedure name
        Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
        Dim procedureName As String = tablename + "_SelectxId"
        Dim fileName As String

        ' Determine the file name to be used
        If createMultipleFiles Then
            fileName = path & procedureName + ".sql"
        Else
            fileName = path & tablename & "_StoredProcedures.sql"

        End If
        Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
            ' Create the seperator
            If (createMultipleFiles = False) Then
                writer.WriteLine()
                writer.WriteLine("/******************************************************************************")
                writer.WriteLine("******************************************************************************/")
            End If

            ' Create the drop statment
            writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
            writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
            writer.WriteLine("GO")
            writer.WriteLine()

            '              ' Create the SQL for the stored procedure
            writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "] (")

            ' Create the parameter list
            Dim i As Integer
            i = 0
            For Each column As DataColumn In claves.PrimaryKey
                'Ignore any identity columns

                Dim row As DataRow = column_metadata(struct, column.ColumnName.ToString)


                'Append the column name as a parameter of the insert statement
                If i > 0 Then
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False) + ",")
                Else
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))


                End If
                i += 1
            Next column
            writer.WriteLine(")")

            writer.WriteLine()
            writer.WriteLine("AS")
            writer.WriteLine()
            writer.WriteLine("SET NOCOUNT ON")
            writer.WriteLine()
            writer.WriteLine("SELECT")
            i = 0
            For Each row As DataRow In struct.Rows
                'Ignore any identity columns



                'Append the column name as a parameter of the insert statement
                If i < struct.Rows.Count - 1 Then
                    writer.WriteLine("[" + row.Item("Column_Name").ToString + "]" + ",")
                Else
                    writer.WriteLine("[" + row.Item("Column_Name").ToString + "]")

                End If

                i += 1
            Next row
            writer.WriteLine()
            writer.WriteLine("FROM")
            writer.WriteLine(Constants.vbTab & "[" & tablename & "]")
            writer.WriteLine("WHERE")
            i = 0
            For Each column As DataColumn In claves.PrimaryKey
                'Ignore any identity columns

                'Append the column name as a parameter of the insert statement
                If i > 0 Then
                    writer.WriteLine(" AND [" + column.ColumnName.ToString + "] = @" + column.ColumnName.ToString + ",")
                Else
                    writer.WriteLine("[" + column.ColumnName.ToString + "] = @" + column.ColumnName.ToString)


                End If
                i += 1
            Next column
            writer.WriteLine()
            writer.WriteLine("GO")
        End Using



    End Sub

    Friend Shared Sub CreateSelectAllProcedure(ByVal struct As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        ' Create the stored procedure name
        Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
        Dim procedureName As String = tablename + "_SelectAll"
        Dim fileName As String

        ' Determine the file name to be used
        If createMultipleFiles Then
            fileName = path & procedureName + ".sql"
        Else
            fileName = path & tablename & "_StoredProcedures.sql"

        End If
        Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
            ' Create the seperator
            If (createMultipleFiles = False) Then
                writer.WriteLine()
                writer.WriteLine("/******************************************************************************")
                writer.WriteLine("******************************************************************************/")
            End If

            ' Create the drop statment
            writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
            writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
            writer.WriteLine("GO")
            writer.WriteLine()

            '              ' Create the SQL for the stored procedure
            writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]")

            writer.WriteLine()
            writer.WriteLine("AS")
            writer.WriteLine()
            writer.WriteLine("SET NOCOUNT ON")
            writer.WriteLine()
            writer.WriteLine("SELECT * ")
            writer.WriteLine()
            writer.WriteLine("FROM")
            writer.WriteLine(Constants.vbTab & "[" & tablename & "]")
            writer.WriteLine()
            writer.WriteLine("GO")
        End Using
    End Sub
    Friend Shared Sub CreateSelectLikeProcedure(ByVal struct As DataTable, ByVal claves As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        For Each row As DataRow In struct.Rows
            If row.Item("SQLData_type").ToString <> "image" Then
                Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
                Dim procedureName As String = tablename + "_TxLike" + row.Item("Column_Name").ToString
                Dim fileName As String

                ' Determine the file name to be used
                If createMultipleFiles Then
                    fileName = path & procedureName + ".sql"
                Else
                    fileName = path & tablename & "_StoredProcedures.sql"

                End If
                Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
                    ' Create the seperator
                    If (createMultipleFiles = False) Then
                        writer.WriteLine()
                        writer.WriteLine("/******************************************************************************")
                        writer.WriteLine("******************************************************************************/")
                    End If

                    ' Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
                    writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
                    writer.WriteLine("GO")
                    writer.WriteLine()

                    '              ' Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "] (")
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))
                    writer.WriteLine(")")
                    writer.WriteLine()
                    writer.WriteLine("AS")
                    writer.WriteLine()
                    writer.WriteLine("SET NOCOUNT ON")
                    writer.WriteLine()
                    writer.WriteLine("SELECT * ")
                    writer.WriteLine()
                    writer.WriteLine("FROM")
                    writer.WriteLine(Constants.vbTab & "[" & tablename & "]")
                    writer.WriteLine()
                    writer.WriteLine("WHERE")
                    If row.Item("SQLData_type").ToString <> "text" Then
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "] Like  '%' + @" + row.Item("Column_Name").ToString + " + '%'")
                    Else
                        writer.WriteLine("[" + row.Item("Column_Name").ToString + "] Like @" + row.Item("Column_Name").ToString)
                    End If
                    writer.WriteLine()
                    writer.WriteLine("GO")
                End Using
            End If

        Next row
    End Sub
    Friend Shared Sub CreateSelectEqualProcedure(ByVal struct As DataTable, ByVal claves As DataTable, ByVal path As String, ByVal createMultipleFiles As Boolean)
        For Each row As DataRow In struct.Rows
            If row.Item("SQLData_type").ToString <> "text" And _
            row.Item("SQLData_type").ToString <> "image" Then
                Dim tablename As String = struct.Rows(0).Item("Table_Name").ToString
                Dim procedureName As String = tablename + "_Tx" + row.Item("Column_Name").ToString
                Dim fileName As String

                ' Determine the file name to be used
                If createMultipleFiles Then
                    fileName = path & procedureName + ".sql"
                Else
                    fileName = path & tablename & "_StoredProcedures.sql"

                End If
                Using writer As StreamWriter = New StreamWriter(fileName, Not createMultipleFiles)
                    ' Create the seperator
                    If (createMultipleFiles = False) Then
                        writer.WriteLine()
                        writer.WriteLine("/******************************************************************************")
                        writer.WriteLine("******************************************************************************/")
                    End If

                    ' Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)")
                    writer.WriteLine("drop procedure [dbo].[" + procedureName + "]")
                    writer.WriteLine("GO")
                    writer.WriteLine()

                    '              ' Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "] (")
                    writer.WriteLine(Constants.vbTab + CreateParameterString(row, False))
                    writer.WriteLine(")")
                    writer.WriteLine()
                    writer.WriteLine("AS")
                    writer.WriteLine()
                    writer.WriteLine("SET NOCOUNT ON")
                    writer.WriteLine()
                    writer.WriteLine("SELECT * ")
                    writer.WriteLine()
                    writer.WriteLine("FROM")
                    writer.WriteLine(Constants.vbTab & "[" & tablename & "]")
                    writer.WriteLine()
                    writer.WriteLine("WHERE")
                    writer.WriteLine("[" + row.Item("Column_Name").ToString + "] = @" + row.Item("Column_Name").ToString)
                    writer.WriteLine()
                    writer.WriteLine("GO")
                End Using
            End If

        Next row
    End Sub

    Private Shared Function CreateParameterString(ByVal row As DataRow, ByVal checkForOutputParameter As Boolean) As String
        Dim parameter As String
        Select Case row.Item("SQLData_type").ToString.ToLower()
            Case "binary"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                "(" + row.Item("COLUMN_LENGTH").ToString + ")"
            Case "bigint"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "bit"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower

            Case "char"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                "(" + row.Item("COLUMN_LENGTH").ToString + ")"
            Case "datetime"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "decimal"
                If (row.Item("NUMERIC_SCALE").ToString = "0") Then
                    parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                    "(" + row.Item("NUMERIC_PRECISION").ToString + ")"
                Else
                    parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                    "(" + row.Item("NUMERIC_PRECISION").ToString + ", " + row.Item("NUMERIC_SCALE").ToString + ")"
                End If
            Case "float"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                    "(" + row.Item("NUMERIC_PRECISION").ToString + ")"
            Case "image"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "int"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "money"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "nchar"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                "(" + row.Item("COLUMN_LENGTH").ToString + ")"
            Case "ntext"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "nvarchar"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
               "(" + row.Item("COLUMN_LENGTH").ToString + ")"
            Case "numeric"
                If (row.Item("NUMERIC_SCALE").ToString = "0") Then
                    parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                    "(" + row.Item("NUMERIC_PRECISION").ToString + ")"
                Else
                    parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
                    "(" + row.Item("NUMERIC_PRECISION").ToString + ", " + row.Item("NUMERIC_SCALE").ToString + ")"
                End If
            Case "real"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "smalldatetime"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "smallint"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "smallmoney"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "sql_variant"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "tinyint"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "varchar"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower + _
               "(" + row.Item("COLUMN_LENGTH").ToString + ")"
            Case "text"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case "image"
                parameter = "@" + row.Item("Column_Name").ToString + " " + row.Item("SQLData_type").ToString.ToLower
            Case Else
                Return ""

        End Select

        If checkForOutputParameter And (CType(row.Item("IS_IDENTITY"), Boolean) Or CType(row.Item("IS_ROWGUIDCOL"), Boolean)) Then
            Return parameter + " OUTPUT"
        Else
            Return parameter
        End If

    End Function
    Private Shared Function es_clave(ByVal claves As DataTable, ByVal nombreCol As String) As Boolean

        For Each column As DataColumn In claves.PrimaryKey
            If column.ColumnName.ToString = nombreCol Then
                Return True
            End If
        Next
        Return False
    End Function
    Private Shared Function column_metadata(ByVal struct As DataTable, ByVal column As String) As DataRow
        For Each rows As DataRow In struct.Rows
            If rows.Item("Column_Name").ToString = column Then
                Return rows
            End If
        Next
    End Function
End Class
